SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




CREATE PROCEDURE [dbo].[usp_SearchVerifiedRequire]
    (
      @JOL_Employer_Account VARCHAR(50),
      @JOL_Employer_Type VARCHAR(50)
    )
AS 
    IF @JOL_Employer_Type = '' 
        SELECT  a.JOL_Employer_Account,
                b.JOL_Require_ID,
                b.JOL_Require_Job,
                b.JOL_Require_Study_Level,
                CASE b.JOL_Require_Student_Type
                  WHEN 2 THEN '实习生'
                  WHEN 1 THEN '应届生'
                END,
                b.JOL_Require_Number,
                b.JOL_Require_AddTime,
                b.JOL_Require_Profession,
                b.OnlyForSysu,
                b.JOL_Require_ClickNumber
        FROM    JOL_Employer AS a
                INNER JOIN JOL_Require as b ON b.JOL_Require_Company = a.JOL_Employer_Account
        WHERE   b.JOL_Require_VerifyStatus = '已审核'
                AND b.JOL_Require_EndTime > GETDATE()
                AND b.JOL_Require_Student_Type = 1
                AND a.JOL_Employer_Account = @JOL_Employer_Account
        ORDER BY b.JOL_Require_AddTime DESC,
                b.JOL_Require_UpdateTime DESC
    ELSE 
        SELECT  a.JOL_Employer_Account,
                b.JOL_Require_ID,
                b.JOL_Require_Job,
                b.JOL_Require_Study_Level,
                CASE b.JOL_Require_Student_Type
                  WHEN 2 THEN '实习生'
                  WHEN 1 THEN '应届生'
                END,
                b.JOL_Require_Number,
                b.JOL_Require_AddTime,
                b.JOL_Require_Profession,
                b.OnlyForSysu,
                b.JOL_Require_ClickNumber
        FROM    JOL_Employer AS a
                INNER JOIN JOL_Require as b ON b.JOL_Require_Company = a.JOL_Employer_Account
        WHERE   b.JOL_Require_VerifyStatus = '已审核'
                AND b.JOL_Require_EndTime > GETDATE()
                AND b.JOL_Require_Student_Type = 1
                AND a.JOL_Employer_Account = @JOL_Employer_Account
                AND a.JOL_Employer_Type = @JOL_Employer_Type
        ORDER BY b.JOL_Require_AddTime DESC,
                b.JOL_Require_UpdateTime DESC



GO
